# 1.编程实现：循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
import random
import string
from datetime import datetime, timedelta
import pymysql
import openpyxl

"""
db = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    port=3306,
    charset='utf8',
    database='0617test'
)
try:
    with db.cursor() as cursor:
        for i in range(0, 10):
            name = input("请输入姓名（中英文都可以）：")
            age = int(input("请输入年龄（0-120）："))
            sex = input("请输入性别（男/女）：")
            height = int(input("请输入身高（）cm："))
            sql = "insert into persons (name,age,sex,height) values (%s,%s,%s,%s)"
            param = (name, age, sex, height)
            cursor.execute(sql, param)
            db.commit()
            print(f"第{i + 1}条信息插入完毕")
except pymysql.Error as e:
    print(f"数据库错误：{e}")
    db.rollback()  # 发生错误时回滚事务
except ValueError as e:
    print(f"输入错误：{e}")
except Exception as e:
    print(f"未知错误：{e}")
finally:
    cursor.close()
    db.close()
"""

# 2.编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
import openpyxl

# 2.1先创建一个表格
wb = openpyxl.Workbook()
# 操作工作簿完毕后需要保存工作
sheet = wb.active
sheet.title = "UserExcelOne"
headers = ["idcard", "username", "realname", "pwd",
           "telphone", "email", "age", "sex",
           "address", "hiredate", "sal", "job", "company"]
sheet.append(headers)


def generate_excel_data(file_path="user_info.xlsx"):
    def generate_row():
        # 生成身份证号码
        idcard = "".join(random.choices(string.digits, k=17)) + random.choice(string.digits + "X")
        # 生成用户名
        username = "user_" + "".join(random.choices(string.ascii_lowercase + string.digits, k=6))
        # 生成真实姓名
        realname = "".join(random.choices("张王李赵刘陈杨黄吴周徐孙马朱胡郭何罗高林孟于董余", k=2))
        # 生成密码
        pwd = "pwd_" + "".join(random.choices(string.ascii_letters + string.digits, k=8))
        # 生成电话
        telphone = "1" + "".join(random.choices(string.digits, k=10))
        # 生成邮箱
        email = username + "@" + random.choice(["example.com", "test.com", "demo.com"])
        # 生成年龄
        age = random.randint(18, 60)
        # 生成性别
        sex = random.choice(["男", "女"])
        # 生成地址
        address = random.choice(["北京市XX区", "上海市XX区", "广州市XX区", "深圳市XX区"]) + "XX街道"
        # 生成入职日期
        hiredate = (datetime.now() - timedelta(days=random.randint(1, 365))).strftime("%Y-%m-%d")
        # 生成工资
        sal = round(random.uniform(3000.00, 7 + 20000.00), 2)
        # 生成职位
        job = random.choice(["工程师", "销售", "设计师", "运营", "客服"])
        # 生成公司
        company = random.choice(["ABC科技", "XYZ集团", "DEF企业", "GHI公司"])
        return [
            idcard, username, realname, pwd,
            telphone, email, age, sex,
            address, hiredate, sal, job, company
        ]

    # 循环生成2000行数据并插入excel中
    for _ in range(2000):
        row_data = generate_row()
        sheet.append(row_data)
    # 保存excel文件
    wb.save(file_path)
    print(f"文件{file_path}已生成，包含2000行数据")


# 2.2从excel中读取数据并写入mysql
# 从Excel文件读取数据并批量写入MySQL数据库
"""
    参数:
        excel_path (str): 输入Excel文件的路径，默认为 "data.xlsx"
    返回:
        None: 直接将数据写入数据库，不返回值
    使用方法:
        excel_to_mysql()  # 使用默认文件名
        excel_to_mysql("custom_data.xlsx")  # 指定文件名
    注意事项:
        1. 需要提前创建好MySQL数据库和表
        2. 表结构需要与Excel表头字段完全匹配
        3. 需要正确配置数据库连接参数
"""


def excel_to_mysql(excel_path="user_info.xlsx"):
    # 连接数据库
    db = pymysql.connect(
        host="localhost",
        user="root",
        password="123456",
        port=3306,
        charset="utf8",
        database="0617test"
    )
    cursor = db.cursor()
    # 打开excel文件
    wb = openpyxl.load_workbook(excel_path)
    sheet = wb.active
    sheet.title = "UserInfo"
    # SQL插入语句（使用executemany批量插入）
    # 注意：字段数量和顺序必须与Excel表头及数据库表结构一致
    sql = """
      INSERT INTO user_info (
          idcard, username, realname, pwd, 
          telphone, email, age, sex, 
          address, hiredate, sal, job, company
      ) 
      VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
      """
    # 将param设为列表，从excel获取数据，一次一行，逐列插入数据
    params = [row for row in sheet.iter_rows(min_row=2, values_only=True)]
    print(params)
    try:
        cursor.executemany(sql, params)
        db.commit()
        print(f"成功插入{len(params)}条数据到数据表user_info中")
    except pymysql.MySQLError as e:
        db.rollback()
        print(f"插入数据失败：{e}")
    finally:
        cursor.close()
        db.close()


# 执行程序
# 1.执行程序1，生成数据插入到excel表
generate_excel_data()
# 2.执行程序2，提取excel表数据提取到0617test数据库中的user_info表中
excel_to_mysql()
